package com.admin.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.cxtj.CxtjBean;

public class OrcUtil {

	public static Connection getConnection(){  
        Connection conn=null;  
        try {  
            Class.forName("oracle.jdbc.driver.OracleDriver");//找到oracle驱动器所在的类  
            String url="jdbc:oracle:thin:@192.168.0.216:1521:ORCL"; //URL地址  
            String username=   "scott";  //   
         //   String username=   "sys as sysdba";  //scott
            String password="123456";              
            conn=DriverManager.getConnection(url, username, password);  
             } catch (ClassNotFoundException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }  
        return conn;  
    }  
    public static void close(PreparedStatement pstmt){  
        if(pstmt !=null){  
            try {  
                pstmt.close();  
            } catch (SQLException e) {  
                  
                e.printStackTrace();  
            }  
        }  
    }  
      
    public static void close(ResultSet rs){  
        if(rs !=null){  
            try {  
                rs.close();  
            } catch (SQLException e) {  
                // TODO Auto-generated catch block  
                e.printStackTrace();  
            }  
        }  
    } 
    
    public static List<CxtjBean> getCxList(String minTime,String maxTime) throws SQLException{
    	String sql = "SELECT rownum,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE  USERNAME not in ('DBSNMP','SYSMAN') ORDER BY TIMESTAMP DESC"; 
    	if(minTime!=null && !"".equals(minTime) && maxTime!=null && !"".equals(maxTime)){
    		sql = "SELECT rownum,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE Timestamp<to_date('"+maxTime+"', 'yyyy-mm-dd hh24:mi:ss') and Timestamp>to_date('"+minTime+"', 'yyyy-mm-dd hh24:mi:ss') and USERNAME not in ('DBSNMP','SYSMAN') ORDER BY TIMESTAMP DESC";  
    	}else{
    		if(minTime!=null && !"".equals(minTime)){
    			sql = "SELECT rownum,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE Timestamp>to_date('"+minTime+"', 'yyyy-mm-dd hh24:mi:ss') and USERNAME not in ('DBSNMP','SYSMAN') ORDER BY TIMESTAMP DESC"; 
	    	}else if(maxTime!=null && !"".equals(maxTime)){
	    		sql = "SELECT rownum,USERNAME, TIMESTAMP, SQL_TEXT FROM DBA_AUDIT_TRAIL WHERE Timestamp<to_date('"+maxTime+"', 'yyyy-mm-dd hh24:mi:ss')  and USERNAME not in ('DBSNMP','SYSMAN') ORDER BY TIMESTAMP DESC"; 
	    	}
    	}
    	System.out.println(sql);
    	Connection conn = null;  
        PreparedStatement pstmt=null;  
        ResultSet rs=null;//定义存放查询结果的结果集          
        List<CxtjBean> cxList = new ArrayList<CxtjBean>();
        try{  
            conn=OrcUtil.getConnection();  
            pstmt=conn.prepareStatement(sql);  
            rs=pstmt.executeQuery();//执行查询操作  
            while(rs.next()){ 
             CxtjBean cx = new CxtjBean();             
             cx.setSqluser(rs.getString("USERNAME"));    
             cx.setCzsj(rs.getTimestamp("TIMESTAMP")); 
          //   cx.setDycs(rs.getInt("rownum"));
             if(rs.getString("SQL_TEXT")==null || "".equals(rs.getString("SQL_TEXT")))
             {
            	 cx.setConsql("null");
             }
             else{
            	 cx.setConsql(rs.getString("SQL_TEXT"));    
             }
             cxList.add(cx);
            }
            System.out.println("total:"+cxList.size()+" || MinTime:"+cxList.get(cxList.size()-1).getCzsj());  
        }catch(SQLException e){  
            e.printStackTrace();  
        }finally{  
            //按顺序进行关闭  
        	OrcUtil.close(rs);  
        	OrcUtil.close(pstmt);  
            conn.close();              
        }    
		return cxList;    	
    }
    
    
    
   /* public static void main(String[] args) throws Exception {   
    	String sql = "select * from emp T9";
        Connection conn = null;  
        PreparedStatement pstmt=null;  
        ResultSet rs = null;
        try{  
            conn=OrcUtil.getConnection();  
            pstmt=conn.prepareStatement(sql);  
            rs=pstmt.executeQuery();//执行查询操作  
            while(rs.next()){ 
             System.out.print(rs.getString("JOB"));                   
            }  
        }catch(SQLException e){  
            e.printStackTrace();  
        }finally{  
            //按顺序进行关闭  
        	OrcUtil.close(rs);  
        	OrcUtil.close(pstmt);  
            conn.close();              
        }        	
    }*/
}
